<%@ page import="instructor.sample.*" %>
<%@ page import="java.text.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.sql.*" %>
<% 
   String today = null;
   Today t = new Today();
   today = t.getToday();
   request.setAttribute("picture","Service2");
   request.setAttribute("title", "Product Listing");  
   request.setAttribute("header", "Welcome : Today is " + today); 
 %>
 <%@ include file="header.jsp"%>
<TD width="850" vAlign="top"> 
<%	
OraDB myDB = new OraDB();
ResultSet rs = null;
ResultSet rs2 = null;
myDB.createConnection();

%>
<br /><h2>Product Listing</h2><br />

<%
String myQuery = "Select modalityid,modalitydescription " +
 "from modality order by modalitydescription asc"; 

rs=myDB.Query(myQuery); 
String mymodalitydesc="";%>	 
<table><tr><td valign="top"><a name=#top></a><h3>Modality: </h3></td>
<%  while (rs.next()) {   
	%>
	<td valign="top"><a href=#<%=rs.getString("modalityid")%>>> <%=rs.getString("modalitydescription")%></a></td>
 <%  } %>
</tr></table> 
<hr>
<table width=100% border=0>
<%
rs.close();
String myQuery2 = "SELECT Modality.modalitydescription as modality, Modality.modalityid as modalityident, Product.productname as productname, "
+ " Customer.customername as customername, Location.City||', '||Location.StateCode as local, TO_CHAR(Equipment.inservicedate,'MM/DD/YYYY') as inservicedate, ServiceContract.ServiceContractDescription as servicecontractdescription "
+ " FROM Product, Customer, Location, Equipment, ServiceContract, Modality "
+ " WHERE Product.productid = Equipment.productid AND Equipment.locationid = Location.locationid AND Modality.modalityid = Product.modalityid "
+ " AND Customer.customerid = Location.customerid AND Customer.ServiceContractID = ServiceContract.ServiceContractID "
+ " ORDER BY Modality.modalitydescription,Product.productname, Customer.customername,local"; 

rs2=myDB.Query(myQuery2); 
String modality="";%>	 
				 
<%  while (rs2.next()) {   
	if (modality.equals(rs2.getString("modality"))) {
	} else { 
		if (!modality.equals("")) { %> <tr><td colspan="5"><br><center><a href="#top">>Back</a></center><hr>
		<% 
		} 
		modality = rs2.getString("modality"); %>
		<br><a name=<%=rs2.getString("modalityident")%>><h3><%=modality%></h3></a></td></tr>
		<tr>
		<th bgcolor="#3B73B9" style="color:white;"><center>Product</center></th>
		<th bgcolor="#3B73B9" style="color:white;"><center>Customer</th>
		<th bgcolor="#3B73B9" style="color:white;"><center>Location</th>
		<th bgcolor="#3B73B9" style="color:white;"><center>In Service Date</th>
		<th bgcolor="#3B73B9" style="color:white;"><center>Service Contract</th>
		</tr>
<%	} %>
        <tr>
        <td><%=rs2.getString("productname")%></td>
		<td><%=rs2.getString("customername")%></td>
		<td><%=rs2.getString("local")%></td>
		<td><%=rs2.getString("inservicedate")%></td>
		<td><%=rs2.getString("servicecontractdescription")%></td>
		</tr>
 <%  } %>
</table>
<%
rs2.close();
myDB.closeConnection(); 
%>
</TD></TR></TABLE></body></html>